using System.Collections.Generic;
using Firestarter.DB.Schema.Logic;
using Habanero.Testability;
using NUnit.Framework;

namespace Firestarter.DB.Schema.Test.Logic
{
    [TestFixture]
    public class TestCreateTableSqlBuilder : TestBase
    {

        protected CreateTableSqlBuilder CreateBuilder()
        {
            return CreateBuilder(GetRandomString());
        }

        private static string GetRandomString()
        {
            return RandomValueGen.GetRandomString();
        }

        protected virtual CreateTableSqlBuilder CreateBuilder(string tableName)
        {
            return new CreateTableSqlBuilder_Stub(tableName);
        }

        protected virtual string Enclose(string name)
        {
            return string.Format("`{0}`", name);
        }

        [Test]
        public void Test_ConstructSqlBuilderCreateTable()
        {
            string tableName = GetRandomString();
            //---------------Set up test pack-------------------
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            CreateTableSqlBuilder createTableSqlBuilderMySql = CreateBuilder(tableName);
            //---------------Test Result -----------------------
            Assert.AreEqual(tableName, createTableSqlBuilderMySql.TableName);
        }

        [Test]
        public void Test_AddColumn_WithLength()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            string columnName = GetRandomString();
            const string dataType = "VARCHAR";
            const int length = 50;
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddColumn(columnName, dataType, length, null, true, false);
            //---------------Test Result -----------------------
            Assert.AreEqual(1, sqlBuilderCreateTable.Columns.Count);
            string expectdColumnString = string.Format("{0} {1}({2}) NULL", Enclose(columnName), dataType, length);
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.Columns[0]);
        }

        [Test]
        public void Test_AddColumn_WithoutLength()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            const string dataType = "CHAR";
            string columnName = GetRandomString();
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddColumn(columnName, dataType, null, null, true, false);
            //---------------Test Result -----------------------
            Assert.AreEqual(1, sqlBuilderCreateTable.Columns.Count);
            string expectdColumnString = string.Format("{0} {1} NULL", Enclose(columnName), dataType);
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.Columns[0]);
        }

        [Test]
        public void Test_AddTwoColumn()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            const string dataType = "VARCHAR";
            const int length = 50;
            string columnName = GetRandomString();
            sqlBuilderCreateTable.AddColumn(columnName, dataType, length, null, true, false);
            const string expected2ndColumnType = "CHAR";
            const int expected2ndColumnLength = 50;
            //---------------Assert Precondition----------------
            Assert.AreEqual(1, sqlBuilderCreateTable.Columns.Count);

            //---------------Execute Test ----------------------
            var expected2ndColumnName = columnName + "_2";
            sqlBuilderCreateTable.AddColumn(expected2ndColumnName, expected2ndColumnType, expected2ndColumnLength, null, true, false);
            //---------------Test Result -----------------------
            Assert.AreEqual(2, sqlBuilderCreateTable.Columns.Count);
            string expectdColumnString = string.Format("{0} {1}({2}) NULL", Enclose(columnName), dataType, length);
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.Columns[0]);
            expectdColumnString = string.Format("{0} {1}({2}) NULL", Enclose(expected2ndColumnName), expected2ndColumnType, expected2ndColumnLength);
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.Columns[1]);
        }

        [Test]
        public virtual void Test_AddColumn_NotNullable_NotAutoIncrement()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            const string dataType = "int";
            string columnName = GetRandomString();
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddColumn(columnName, dataType, null, null, false, false);
            //---------------Test Result -----------------------
            string expectdSql = string.Format("{0} {1} NOT NULL", Enclose(columnName), dataType);
            Assert.AreEqual(expectdSql, sqlBuilderCreateTable.Columns[0]);
        }

        [Test]
        public virtual void Test_AddColumn_NotNullable_AutoIncrement()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            const string dataType = "int";
            string columnName = GetRandomString();
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddColumn(columnName, dataType, null, null, false, true);
            //---------------Test Result -----------------------
            string expectdSql = string.Format("{0} {1} NOT NULL AUTO_INCREMENT", Enclose(columnName), dataType);
            Assert.AreEqual(expectdSql, sqlBuilderCreateTable.Columns[0]);
        }

        [Test]
        public virtual void Test_AddColumn_Nullable_NotAutoIncrement()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            const string dataType = "int";
            string columnName = GetRandomString();
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddColumn(columnName, dataType, null, null, true, false);
            //---------------Test Result -----------------------
            string expectdSql = string.Format("{0} {1} NULL", Enclose(columnName), dataType);
            Assert.AreEqual(expectdSql, sqlBuilderCreateTable.Columns[0]);
        }

        [Test]
        public virtual void Test_AddColumn_Nullable_AutoIncrement()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            const string dataType = "int";
            string columnName = GetRandomString();
            //-------------Assert Preconditions -------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddColumn(columnName, dataType, null, null, true, true);
            //---------------Test Result -----------------------
            string expectdSql = string.Format("{0} {1} NULL AUTO_INCREMENT", Enclose(columnName), dataType);
            Assert.AreEqual(expectdSql, sqlBuilderCreateTable.Columns[0]);
        }

        [Test]
        public void Test_AddPrimaryKeyColumn()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            string columnName = GetRandomString();
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddPrimaryKeyColumn(columnName);
            //---------------Test Result -----------------------
            Assert.AreEqual(1, sqlBuilderCreateTable.PrimaryKeyColumns.Count);
            string expectdColumnString = Enclose(columnName);
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.PrimaryKeyColumns[0]);
        }

        [Test]
        public void Test_AddTwoPrimaryKeyColumns()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            string columnName = GetRandomString();
            sqlBuilderCreateTable.AddPrimaryKeyColumn(columnName);
            //---------------Assert Precondition----------------
            Assert.AreEqual(1, sqlBuilderCreateTable.PrimaryKeyColumns.Count);

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddPrimaryKeyColumn(columnName + "_2");
            //---------------Test Result -----------------------
            Assert.AreEqual(2, sqlBuilderCreateTable.PrimaryKeyColumns.Count);
            string expectdColumnString = Enclose(columnName);
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.PrimaryKeyColumns[0]);
            expectdColumnString = Enclose(columnName + "_2");
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.PrimaryKeyColumns[1]);
        }

        [Test]
        public virtual void Test_AddForeignKey_ShouldCreateFKConstraint()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            string referencedTableName = GetRandomString();
            string foreignKeyName = GetRandomString();
            const string parentColumnNameList = "field1FK, field2FK";
            string expectedParentColumnNameList = Enclose("field1FK") + ", " + Enclose("field2FK");
            const string referencedColumnNameList = "field1, field2";
            string expectedReferencedColumnNameList = Enclose("field1") + ", " + Enclose("field2");
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddForeignKey
                (foreignKeyName, parentColumnNameList, referencedTableName, referencedColumnNameList);
            //---------------Test Result -----------------------
            Assert.AreEqual(1, sqlBuilderCreateTable.ForeignKeys.Count);
            string expectdFKString = string.Format
                ("CONSTRAINT {0} FOREIGN KEY {0} ({1}) REFERENCES {2} ({3})", Enclose(foreignKeyName), expectedParentColumnNameList,
                 Enclose(referencedTableName), expectedReferencedColumnNameList);
            Assert.AreEqual(expectdFKString, sqlBuilderCreateTable.ForeignKeys[0]);
        }

        [Test]
        public void Test_AddTwoForeignKeys()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            string referencedTableName = GetRandomString();
            string foreignKeyName = GetRandomString();
            const string parentColumnNameList = "field1FK, field2FK";
            const string referencedColumnNameList = "field1, field2";
            sqlBuilderCreateTable.AddForeignKey
                (foreignKeyName, parentColumnNameList, referencedTableName, referencedColumnNameList);
            //---------------Assert Precondition -----------------------
            Assert.AreEqual(1, sqlBuilderCreateTable.ForeignKeys.Count);
            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddForeignKey
                (foreignKeyName + 1, parentColumnNameList + 1, referencedTableName + 1, referencedColumnNameList + 1);
            //---------------Test Result -----------------------
            Assert.AreEqual(2, sqlBuilderCreateTable.ForeignKeys.Count);
        }


        //CONSTRAINT `Index_2` UNIQUE (`col1`, `col2`)
        [Test]
        public virtual void Test_AddUniqueKey_Simple()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            string columnName = GetRandomString();
            const string alternatekeyname = "uniqueKeyname";
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            sqlBuilderCreateTable.AddUniqueKey(alternatekeyname, columnName);
            //---------------Test Result -----------------------
            Assert.AreEqual(1, sqlBuilderCreateTable.UniqueKeys.Count);
            string expectdColumnString = string.Format("CONSTRAINT {0} UNIQUE ({1})", Enclose(alternatekeyname), Enclose(columnName));
            Assert.AreEqual(expectdColumnString, sqlBuilderCreateTable.UniqueKeys[0]);
        }

        [Test]
        public virtual void Test_ForeignKeyGetSQL()
        {
            //---------------Set up test pack-------------------
            string tableName = GetRandomString();
            CreateTableSqlBuilder sqlBuilderCreateTable = CreateBuilder(tableName);
            string referencedTableName = GetRandomString();
            string foreignKeyName = GetRandomString();
            const string parentColumnNameList = "field1FK, field2FK";
            string expectedParentColumnNameList = Enclose("field1FK") + ", " + Enclose("field2FK");
            const string referencedColumnNameList = "field1, field2";
            string expectedReferencedColumnNameList = Enclose("field1") + ", " + Enclose("field2");
            sqlBuilderCreateTable.AddForeignKey
                (foreignKeyName, parentColumnNameList, referencedTableName, referencedColumnNameList);
            //---------------Assert Precondition----------------

            //---------------Execute Test ----------------------
            string sql = sqlBuilderCreateTable.GetSql();
            //---------------Test Result -----------------------
            Assert.AreEqual(1, sqlBuilderCreateTable.ForeignKeys.Count);
            string expectdFKString = string.Format
                ("CONSTRAINT {0} FOREIGN KEY {0} ({1}) REFERENCES {2} ({3})", Enclose(foreignKeyName), expectedParentColumnNameList, 
                Enclose(referencedTableName), expectedReferencedColumnNameList);
            StringAssert.Contains(expectdFKString, sql);
        }

        public class CreateTableSqlBuilder_Stub : CreateTableSqlBuilder_MySql
        {
            public CreateTableSqlBuilder_Stub(string tableName)
                : base(tableName)
            {
            }
/*
            public new List<string> ForeignKeys
            {
                get { return base.ForeignKeys; }
            }

            public new string TableName
            {
                get { return base.TableName; }
            }

            public new List<string> Columns
            {
                get { return base.Columns; }
            }

            public new List<string> PrimaryKeyColumns
            {
                get { return base.PrimaryKeyColumns; }
            }

            public new List<string> UniqueKeys
            {
                get { return base.UniqueKeys; }
            }*/
        }
    }
}